--- layout: documentation title: Malloy Documentation footer: /generated/footers/examples/faa.html ---

NTSB Flight Database examples

The follow examples all run against the model at the bottom of this page OR you can find the source code here.

Airport Dashboard

Where can you fly from SJC? For each destination; Which carriers? How long have they been flying there? Are they ontime?

explore flights : [origin.code : 'SJC']
| airport_dashboard
destination_code
LAX
destination
LOS ANGELES INTL
flight_count
475
carriers_by_month
24681012dep_month02040flight_countAmericanSouthwestnickname
routes_map
475flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.01.52.0flight_count32196delay
destination_code
SEA
destination
SEATTLE-TACOMA INTL
flight_count
368
carriers_by_month
24681012dep_month01020flight_countAlaskaAmericanSouthwestnickname
routes_map
368flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.01.52.0flight_count31294delay
destination_code
SAN
destination
SAN DIEGO INTL-LINDBERGH FLD
flight_count
363
carriers_by_month
24681012dep_month0102030flight_countAmericanSouthwestnickname
routes_map
363flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.01.52.0flight_count50100150delay
destination_code
PHX
destination
PHOENIX SKY HARBOR INTL
flight_count
314
carriers_by_month
24681012dep_month01020flight_countAmerica WestAmericanSouthwestnickname
routes_map
314flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.0flight_count50100150delay
destination_code
LAS
destination
MC CARRAN INTL
flight_count
303
carriers_by_month
24681012dep_month05101520flight_countAmerica WestAmericanSouthwestnickname
routes_map
303flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.01.52.0flight_count31126delay
destination_code
BUR
destination
BURBANK-GLENDALE-PASADENA
flight_count
240
carriers_by_month
24681012dep_month01020flight_countSouthwestnickname
routes_map
240flight_count
delay_by_hour_of_day
05101520dep_hour0123flight_count32190delay
destination_code
ORD
destination
CHICAGO O'HARE INTL
flight_count
233
carriers_by_month
24681012dep_month01020flight_countAmericanUnitednickname
routes_map
233flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.0flight_count31343delay
destination_code
ONT
destination
ONTARIO INTL
flight_count
232
carriers_by_month
24681012dep_month01020flight_countSouthwestnickname
routes_map
232flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.01.52.0flight_count32146delay
destination_code
PDX
destination
PORTLAND INTL
flight_count
223
carriers_by_month
24681012dep_month05101520flight_countAlaskaAmericanSouthwestnickname
routes_map
223flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.0flight_count33204delay
destination_code
RNO
destination
RENO/TAHOE INTERNATIONAL
flight_count
196
carriers_by_month
24681012dep_month01020flight_countSouthwestnickname
routes_map
196flight_count
delay_by_hour_of_day
05101520dep_hour0.00.51.01.52.0flight_count32139delay

Carrier Dashboard

Tell me everything about a carrier. How many destinations?, flights? hubs? What kind of planes to they use? How many flights over time? What are the major hubs? For each destionation, How many flights? Where can you? Have they been flying there long? Increasing or decresing year by year? Any seasonality?

explore flights : [carriers.nickname : 'Jetblue']
| carrier_dashboard
destination_count
32
flight_count
4,842
by_manufacturer
0.00.20.40.60.81.0aircraft_countAIRBUS INDUSTRIEAIRBUS4494,393flight_count
by_month
24681012dep_month0200400flight_count
hubs
hub destination_​count
JFK - JOHN F KENNEDY INTL 26
BOS - GENERAL EDWARD LAWRENCE LOGAN INTL 9
LGB - LONG BEACH /DAUGHERTY FIELD/ 8
EWR - NEWARK INTL 6
FLL - FORT LAUDERDALE/HOLLYWOOD INTL 6
IAD - WASHINGTON DULLES INTERNATIONAL 5
OAK - METROPOLITAN OAKLAND INTL 5
PBI - PALM BEACH INTL 3
RSW - SOUTHWEST FLORIDA INTL 3
LAS - MC CARRAN INTL 3
origin_code
JFK
origin
JOHN F KENNEDY INTL
city
NEW YORK
flight_count
1,752
destinations_by_month
24681012dep_month01020flight_countBQN - RAFAEL HERNA…BTV - BURLINGTON I…BUF - BUFFALO NIAG…BUR - BURBANK-GLEN…DEN - DENVER INTLFLL - FORT LAUDERD…LAS - MC CARRAN IN…LGB - LONG BEACH /…MCO - ORLANDO INTLMSY - NEW ORLEANS …OAK - METROPOLITAN…ONT - ONTARIO INTLPBI - PALM BEACH I…PDX - PORTLAND INTLPHX - PHOENIX SKY …PSE - MERCEDITAROC - GREATER ROCH…RSW - SOUTHWEST FL…SAN - SAN DIEGO IN…SEA - SEATTLE-TACO…SJC - SAN JOSE INT…SJU - LUIS MUNOZ M…SLC - SALT LAKE CI…SMF - SACRAMENTO I…SYR - SYRACUSE HAN…TPA - TAMPA INTLname
routes_map
50100150200flight_count
year_over_year
24681012dep_month020406080flight_count2,0032,0042,005dep_year
origin_code
LGB
origin
LONG BEACH /DAUGHERTY FIELD/
city
LONG BEACH
flight_count
471
destinations_by_month
24681012dep_month051015flight_countATL - THE WILLIAM …BOS - GENERAL EDWA…FLL - FORT LAUDERD…IAD - WASHINGTON D…JFK - JOHN F KENNE…LAS - MC CARRAN IN…OAK - METROPOLITAN…SLC - SALT LAKE CI…name
routes_map
15149flight_count
year_over_year
24681012dep_month01020flight_count2,0032,0042,005dep_year
origin_code
FLL
origin
FORT LAUDERDALE/HOLLYWOOD INTL
city
FORT LAUDERDALE
flight_count
375
destinations_by_month
24681012dep_month0102030flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLIAD - WASHINGTON D…JFK - JOHN F KENNE…LGA - LA GUARDIALGB - LONG BEACH /…name
routes_map
50100150200flight_count
year_over_year
24681012dep_month01020flight_count2,0032,0042,005dep_year
origin_code
OAK
origin
METROPOLITAN OAKLAND INTL
city
OAKLAND
flight_count
297
destinations_by_month
24681012dep_month051015flight_countATL - THE WILLIAM …BOS - GENERAL EDWA…IAD - WASHINGTON D…JFK - JOHN F KENNE…LGB - LONG BEACH /…name
routes_map
20406080100flight_count
year_over_year
24681012dep_month051015flight_count2,0032,0042,005dep_year
origin_code
MCO
origin
ORLANDO INTL
city
ORLANDO
flight_count
245
destinations_by_month
24681012dep_month05101520flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…name
routes_map
50100150flight_count
year_over_year
24681012dep_month051015flight_count2,0032,0042,005dep_year
origin_code
IAD
origin
WASHINGTON DULLES INTERNATIONAL
city
WASHINGTON
flight_count
205
destinations_by_month
24681012dep_month0510flight_countFLL - FORT LAUDERD…LGB - LONG BEACH /…OAK - METROPOLITAN…SAN - SAN DIEGO IN…SMF - SACRAMENTO I…name
routes_map
20406080flight_count
year_over_year
24681012dep_month051015flight_count2,0032,0042,005dep_year
origin_code
BOS
origin
GENERAL EDWARD LAWRENCE LOGAN INTL
city
BOSTON
flight_count
185
destinations_by_month
24681012dep_month02468flight_countDEN - DENVER INTLFLL - FORT LAUDERD…LAS - MC CARRAN IN…LGB - LONG BEACH /…MCO - ORLANDO INTLOAK - METROPOLITAN…RSW - SOUTHWEST FL…SJC - SAN JOSE INT…TPA - TAMPA INTLname
routes_map
1020304050flight_count
year_over_year
24681012dep_month051015flight_count2,004.02,004.52,005.0dep_year
origin_code
LAS
origin
MC CARRAN INTL
city
LAS VEGAS
flight_count
146
destinations_by_month
24681012dep_month0510flight_countBOS - GENERAL EDWA…JFK - JOHN F KENNE…LGB - LONG BEACH /…name
routes_map
20406080flight_count
year_over_year
24681012dep_month0510flight_count2,0032,0042,005dep_year
origin_code
PBI
origin
PALM BEACH INTL
city
WEST PALM BEACH
flight_count
143
destinations_by_month
24681012dep_month01020flight_countEWR - NEWARK INTLJFK - JOHN F KENNE…LGA - LA GUARDIAname
routes_map
1138flight_count
year_over_year
24681012dep_month0510flight_count2,0032,0042,005dep_year
origin_code
TPA
origin
TAMPA INTL
city
TAMPA
flight_count
141
destinations_by_month
24681012dep_month0510flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…name
routes_map
20406080100flight_count
year_over_year
24681012dep_month02468flight_count2,0032,0042,005dep_year

Kayak Example Query

Suppose you wanted to build a website like Kayak. Let's assume that the data we have is in the future instead ofthe past. The query below will fetch all the data needed to render a Kayak page in a singe query.

explore flights : [
  origin.code : 'SJC',
  destination.code : 'LAX'|'BUR',
  dep_time : @2004-01-01
]
| kayak
carriers by_​hour flights
nickname flight_​count
dep_​hour flight_​count
dep_​minute name flight_​num origin_​code destination_​code manufacturer model

Sessionizing Flight Data.

You can think of flight data as event data. The below is a classic map/reduce roll up of the filght data by carrier and day, plane and day, and individual events for each plane.

sessionize is ( reduce
  flight_date is dep_time.`date`
  carrier
  daily_flight_count is flight_count
  per_plane_data is (reduce top 20
    tail_num
    plane_flight_count is flight_count
    flight_legs is (reduce order by 2
      tail_num
      dep_minute is dep_time.minute
      origin_code
      dest_code is destination_code
      dep_delay
      arr_delay
    )
  )
)
explore flights : [carrier:'WN', dep_time: @2002-03-03]
| sessionize
dep_​time_​date carrier daily_​flight_​count per_​plane_​data
2002-03-03 WN 37
tail_​num plane_​flight_​count flight_​legs
N735SA 9
tail_​num dep_​minute origin_​code destination_​code dep_​delay arr_​delay
N735SA 2002-03-03 07:15 OAK LAS 0 -10
N735SA 2002-03-03 09:00 LAS SMF 0 5
N735SA 2002-03-03 11:00 SMF SAN 15 10
N735SA 2002-03-03 12:45 SAN LAS 20 17
N735SA 2002-03-03 14:14 LAS SLC 19 19
N735SA 2002-03-03 16:55 SLC PHX 25 10
N735SA 2002-03-03 18:53 PHX SLC 18 10
N735SA 2002-03-03 20:45 SLC RNO 15 10
N735SA 2002-03-03 21:27 RNO PDX 12 5
N641SW 7
tail_​num dep_​minute origin_​code destination_​code dep_​delay arr_​delay
N641SW 2002-03-03 09:15 OAK ONT 0 -1
N641SW 2002-03-03 10:50 ONT OAK 0 0
N641SW 2002-03-03 12:35 OAK PDX 10 5
N641SW 2002-03-03 14:25 PDX OAK 0 -3
N641SW 2002-03-03 16:37 OAK LAX 7 0
N641SW 2002-03-03 18:15 LAX ELP 0 7
N641SW 2002-03-03 21:30 ELP MAF 0 0
N628SW 7
tail_​num dep_​minute origin_​code destination_​code dep_​delay arr_​delay
N628SW 2002-03-03 08:00 OAK LAX 0 -7
N628SW 2002-03-03 10:00 LAX OAK 0 5
N628SW 2002-03-03 11:45 OAK GEG 10 0
N628SW 2002-03-03 13:55 GEG SEA 0 -5
N628SW 2002-03-03 15:25 SEA RNO 0 -12
N628SW 2002-03-03 17:30 RNO LAS 0 -10
N628SW 2002-03-03 19:23 LAS PHX 13 20
N391SW 5
tail_​num dep_​minute origin_​code destination_​code dep_​delay arr_​delay
N391SW 2002-03-03 07:50 BWI PVD 0 0
N391SW 2002-03-03 09:44 PVD TPA 14 19
N391SW 2002-03-03 13:50 TPA PVD 35 48
N391SW 2002-03-03 17:08 PVD BNA 38 61
N391SW 2002-03-03 19:40 BNA HOU 50 60
N791SW 5
tail_​num dep_​minute origin_​code destination_​code dep_​delay arr_​delay
N791SW 2002-03-03 08:00 TPA LAS 0 12
N791SW 2002-03-03 10:37 LAS MDW 17 22
N791SW 2002-03-03 16:45 MDW STL 25 25
N791SW 2002-03-03 18:15 STL MCO 20 10
N791SW 2002-03-03 22:05 MCO FLL 20 20
N723SW 4
tail_​num dep_​minute origin_​code destination_​code dep_​delay arr_​delay
N723SW 2002-03-03 08:40 PHX PVD 0 -16
N723SW 2002-03-03 15:55 PVD PHX 0 -30
N723SW 2002-03-03 20:20 PHX SAN 5 -12
N723SW 2002-03-03 21:00 SAN PHX 0 0

The Malloy Model

All of the queries above are executed against the following model:

export define airports is (explore 'malloy-data.faa.airports'
  primary key code
  name is concat(code, ' - ', full_name)
  airport_count is count()
);

define carriers is (explore 'malloy-data.faa.carriers'
  primary key code
  carrier_count is count()
);

define aircraft_models is (explore 'malloy-data.faa.aircraft_models'
  primary key aircraft_model_code
  aircraft_model_count is count()
);

define aircraft is (explore 'malloy-data.faa.aircraft'
  primary key tail_num
  aircraft_count is count()
  -- joins
  aircraft_models is join on aircraft_model_code
);

export define flights_base is (explore 'malloy-data.faa.flights'
  primary key id2
);

define aircraft_facts is (explore
  (explore flights_base | reduce
    tail_num
    lifetime_flights is count()
    lifetime_distance is distance.sum()
  )
  lifetime_flights_bucketed is floor(lifetime_flights/1000)*1000
);

export define flights is (explore flights_base
  primary key id2
  -- rename some fields
  origin_code renames origin
  destination_code renames destination

  -- joins
  carriers is join on carrier
  origin is join airports on origin_code
  destination is join airports on destination_code,
  aircraft is join on tail_num
  aircraft_facts is join on tail_num

  -- measures
  flight_count is count()
  total_distance is sum(distance)
  seats_for_sale is sum(aircraft.aircraft_models.seats)
  seats_owned is aircraft.sum(aircraft.aircraft_models.seats)

  -- queries
  measures is (reduce
    flight_count
    aircraft.aircraft_count
    dest_count is destination.airport_count
    origin_count is origin.airport_count
  )

  -- shows carriers and number of destinations (bar chart)
  by_carrier is (reduce
    carriers.nickname
    flight_count
    destination_count is destination.count()
  )

  -- shows year over year growth (line chart)
  year_over_year is (reduce
    dep_month is month(dep_time)
    flight_count
    dep_year is dep_time.year
  )

  -- shows plane manufacturers and frequency of use
  by_manufacturer is (reduce top 5
    aircraft.aircraft_models.manufacturer
    aircraft.aircraft_count
    flight_count
  )

  delay_by_hour_of_day is (reduce : [dep_delay >30]
    dep_hour is hour(dep_time)
    flight_count
    delay is FLOOR(dep_delay)/30 * 30
  )

  carriers_by_month is (reduce
    dep_month is dep_time.month
    flight_count
    carriers.nickname
  )

  seats_by_distance is (reduce
    -- seats rounded to 5
    seats is floor(aircraft.aircraft_models.seats/5)*5
    flight_count
    -- distance rounded to 20
    distance is floor(distance/20)*20
  )

  routes_map is (reduce
    origin.latitude
    origin.longitude
    latitude2 is destination.latitude
    longitude2 is destination.longitude
    flight_count
  )

  destinations_by_month is (reduce
    dep_time.`month`
    flight_count
    destination.name
  )

  -- explore flights : [origin.code : 'SJC'] | airport_dashboard
  airport_dashboard is ( reduce top 10
    code is destination_code
    destination is destination.full_name
    flight_count
    carriers_by_month
    routes_map
    delay_by_hour_of_day
  )

  plane_usage is (reduce order by 1 desc : [aircraft.aircraft_count > 1]
    aircraft_facts.lifetime_flights_bucketed
    aircraft.aircraft_count
    flight_count
    by_manufacturer
    by_carrier
  )

  -- explore flights : [carriers.nickname : 'Southwest'] | carrier_dashboard
  carrier_dashboard is ( reduce
    destination_count is destination.airport_count
    flight_count
    by_manufacturer
    by_month is (reduce
      dep_month is dep_time.month
      flight_count
    )
    hubs is (reduce : [destination.airport_count > 1] top 10
      hub is origin.name
      destination_count is destination.airport_count
    )
    origin_dashboard is (reduce top 10
      code is origin_code
      origin is origin.full_name
      origin.city
      flight_count
      destinations_by_month
      routes_map
      year_over_year
    )
  )

  detail is (project top 30 order by 2
    id2, dep_time, tail_num, carrier, origin_code, destination_code, distance
    aircraft.aircraft_model_code
  )

  -- query that you might run for to build a flight search interface
  --   explore flights : [origin.code: 'SJC', destination.code:'LAX'|'BUR', dep_time: @2004-01-01] | kayak
  kayak is (reduce
    carriers is (reduce
      carriers.nickname
      flight_count
    )
    by_hour is (reduce order by 1
      dep_hour is hour(dep_time)
      flight_count
    )
    flights is (reduce
      dep_minute is dep_time.minute
      carriers.name
      flight_num
      origin_code
      destination_code
      aircraft.aircraft_models.manufacturer
      aircraft.aircraft_models.model
    )
  )

  -- example query that shows how you can build a map reduce job to sessionize flights
  sessionize is (reduce
    flight_date is dep_time.`date`
    carrier
    daily_flight_count is flight_count
    per_plane_data is (reduce top 20
      tail_num
      plane_flight_count is flight_count
      flight_legs is (reduce order by 2
        tail_num
        dep_minute is dep_time.minute
        origin_code
        dest_code is destination_code
        dep_delay
        arr_delay
      )
    )
  )

  search_index is (index : [dep_time: @2004-01]
    *, carriers.*,
    origin.code, origin.state, origin.city, origin.full_name, origin.fac_type
    destination.code, destination.state, destination.city, destination.full_name
    aircraft.aircraft_model_code, aircraft.aircraft_models.manufacturer
    aircraft.aircraft_models.model
    on flight_count
  )
);

Data Styles

The data styles tell the Malloy renderer how to render different kinds of results.

{
  "by_carrier": {
    "renderer": "bar_chart"
  },
  "year_over_year": {
    "renderer": "line_chart"
  },
  "by_month": {
    "renderer": "line_chart"
  },
  "by_manufacturer": {
    "renderer": "bar_chart"
  },
  "routes_map": {
    "renderer": "segment_map"
  },
  "destinations_by_month": {
    "renderer": "line_chart"
  },
  "delay_by_hour_of_day": {
    "renderer" : "scatter_chart"
  },
  "seats_by_distance": {
    "renderer": "scatter_chart"
  },
  "carriers_by_month" : {
    "renderer": "line_chart"
  }
}